{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "import numpy as np\n",
    "# import matplotlib.pyplot as plt\n",
    "# import seaborn as sns\n",
    "# %matplotlib inline\n",
    "import requests\n",
    "import sqlalchemy as sql\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "from database_config import db_postgres\n",
    "\n",
    "host, port, user, password, database = db_postgres()\n",
    "\n",
    "engine = sql.create_engine(f\"postgresql+psycopg2://{user}:{password}@{host}:{port}/{database}\")\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore') # отключаем предупреждения\n",
    "\n",
    "# подключаем возможность отключения проверки сертификата\n",
    "from requests.packages.urllib3.exceptions import InsecureRequestWarning\n",
    "requests.packages.urllib3.disable_warnings(InsecureRequestWarning)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Здесь находится инструкция API : https://nsi.rosminzdrav.ru/port/swagger-ui.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# личный токен\n",
    "token = 'a0afc06d-12fa-4868-814a-5309de3ea6eb'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Идентификаторы справочников НСИ\n",
    "frmo = '1.2.643.5.1.13.13.11.1461' # Федеральный реестр медицинский организаций\n",
    "frmo_sp = '1.2.643.5.1.13.13.99.2.114' # Справочник структурных подразделений\n",
    "frmo_ok = '1.2.643.5.1.13.13.99.2.115' # Справочник отделений и кабинентов\n",
    "# В случае, если не указана версия справочника, то возвращаются данные из актуальной версии.\n",
    "dictionary_all_3_items = ['1.2.643.5.1.13.13.11.1461', '1.2.643.5.1.13.13.99.2.114', '1.2.643.5.1.13.13.99.2.115']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Запрос выполнен успешно\n"
     ]
    }
   ],
   "source": [
    "# Создаем запрос на проверку статуса подключения\n",
    "url = 'http://nsi.rosminzdrav.ru/'\n",
    "headers = {\n",
    "    'Authorization': f'Bearer {token}'\n",
    "}\n",
    "response = requests.get(url, headers=headers, verify=False)\n",
    "# Проверяем статус ответа\n",
    "if response.status_code == 200:\n",
    "    print('Запрос выполнен успешно')\n",
    "else:\n",
    "    print(f'Ошибка: {response.status_code}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>version</th>\n",
       "      <th>createDate</th>\n",
       "      <th>publishDate</th>\n",
       "      <th>lastUpdate</th>\n",
       "      <th>releaseNotes</th>\n",
       "      <th>archive</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6.1266</td>\n",
       "      <td>13.01.2017</td>\n",
       "      <td>15.03.2024 00:16</td>\n",
       "      <td>02.03.2024 00:27</td>\n",
       "      <td>Регистр медицинских организаций Российской Фед...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.1623</td>\n",
       "      <td>22.08.2018</td>\n",
       "      <td>15.03.2024 00:17</td>\n",
       "      <td>23.05.2023 01:22</td>\n",
       "      <td>Справочник структурных подразделений, содержит...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.1558</td>\n",
       "      <td>22.08.2018</td>\n",
       "      <td>15.03.2024 00:17</td>\n",
       "      <td>23.05.2023 03:03</td>\n",
       "      <td>Справочник отделений и кабинетов, содержит дан...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  version  createDate       publishDate        lastUpdate  \\\n",
       "0  6.1266  13.01.2017  15.03.2024 00:16  02.03.2024 00:27   \n",
       "1  2.1623  22.08.2018  15.03.2024 00:17  23.05.2023 01:22   \n",
       "2  2.1558  22.08.2018  15.03.2024 00:17  23.05.2023 03:03   \n",
       "\n",
       "                                        releaseNotes  archive  \n",
       "0  Регистр медицинских организаций Российской Фед...    False  \n",
       "1  Справочник структурных подразделений, содержит...    False  \n",
       "2  Справочник отделений и кабинетов, содержит дан...    False  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# проверка версий справочников\n",
    "url1 = 'http://nsi.rosminzdrav.ru/port/rest/versions'\n",
    "versions = []\n",
    "for items in dictionary_all_3_items:\n",
    "    params = {\n",
    "        'identifier': items,\n",
    "        'size': '1',\n",
    "        'sortingDirection': 'ASC',\n",
    "        'userKey': token\n",
    "    }\n",
    "    response = requests.get(url1, params=params, stream=True)\n",
    "    data = response.json()\n",
    "    versions.append(data)\n",
    "df_hist_dictionary = pd.json_normalize(versions, record_path='list')\n",
    "df_hist_dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3 entries, 0 to 2\n",
      "Data columns (total 6 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   version       3 non-null      object\n",
      " 1   createDate    3 non-null      object\n",
      " 2   publishDate   3 non-null      object\n",
      " 3   lastUpdate    3 non-null      object\n",
      " 4   releaseNotes  3 non-null      object\n",
      " 5   archive       3 non-null      bool  \n",
      "dtypes: bool(1), object(5)\n",
      "memory usage: 251.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df_hist_dictionary.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Загрузка медицинских организаций:\n",
    "- **depart_oid** ––– OID структурного подразделения;\n",
    "- **building_address_aoid_area** --- Идентификатор населенного пункта;\n",
    "- **building_address_aoid_street** ––– Идентификатор улицы;\n",
    "- **building_address_houseid** ––– Идентификатор дома."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "93544"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# загрузка справочника\n",
    "url2 = 'http://nsi.rosminzdrav.ru/port/rest/data'\n",
    "params = {\n",
    "    'identifier': frmo,\n",
    "    'size': '1000000',\n",
    "    'sortingDirection': 'ASC',\n",
    "    'userKey': token\n",
    "}\n",
    "response = requests.get(url2, params=params, stream=True)\n",
    "data = response.json()['list']\n",
    "# проверяем длину списка\n",
    "len(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>oid</th>\n",
       "      <th>oldOid</th>\n",
       "      <th>nameFull</th>\n",
       "      <th>nameShort</th>\n",
       "      <th>parentId</th>\n",
       "      <th>medicalSubjectId</th>\n",
       "      <th>medicalSubjectName</th>\n",
       "      <th>inn</th>\n",
       "      <th>kpp</th>\n",
       "      <th>...</th>\n",
       "      <th>prefixStreet</th>\n",
       "      <th>house</th>\n",
       "      <th>building</th>\n",
       "      <th>struct</th>\n",
       "      <th>parentOspOid</th>\n",
       "      <th>ospOid</th>\n",
       "      <th>childrenOspOid</th>\n",
       "      <th>deleteDate</th>\n",
       "      <th>createDate</th>\n",
       "      <th>modifyDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1678</td>\n",
       "      <td>1.2.643.5.1.13.13.12.2.77.7799</td>\n",
       "      <td>None</td>\n",
       "      <td>ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕН...</td>\n",
       "      <td>ФГБУ \"НМИЦ ОНКОЛОГИИ ИМ. Н.Н. БЛОХИНА\" МИНЗДРА...</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>Медицинская организация</td>\n",
       "      <td>7724075162</td>\n",
       "      <td>772401001</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>24</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>16.10.2016</td>\n",
       "      <td>19.05.2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1683</td>\n",
       "      <td>1.2.643.5.1.13.13.12.2.22.1748</td>\n",
       "      <td>None</td>\n",
       "      <td>КРАЕВОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ З...</td>\n",
       "      <td>КГБУЗ \"АКОД\"</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>Медицинская организация</td>\n",
       "      <td>2225022328</td>\n",
       "      <td>222501001</td>\n",
       "      <td>...</td>\n",
       "      <td>тракт</td>\n",
       "      <td>110К</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>16.10.2016</td>\n",
       "      <td>01.09.2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1724</td>\n",
       "      <td>1.2.643.5.1.13.13.12.2.77.7798</td>\n",
       "      <td>1.2.643.5.1.13.3.25.77.31</td>\n",
       "      <td>ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ НАУЧНОЕ ...</td>\n",
       "      <td>ФГБНУ \"НИИ МТ\"</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>Медицинская организация</td>\n",
       "      <td>7719022912</td>\n",
       "      <td>771901001</td>\n",
       "      <td>...</td>\n",
       "      <td>ул</td>\n",
       "      <td>12</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>16.10.2016</td>\n",
       "      <td>09.11.2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1730</td>\n",
       "      <td>1.2.643.5.1.13.13.12.2.73.7369</td>\n",
       "      <td>None</td>\n",
       "      <td>ГОСУДАРСТВЕННОЕ УЧРЕЖДЕНИЕ ЗДРАВООХРАНЕНИЯ \"УЛ...</td>\n",
       "      <td>ГУЗ \"МИАЦ\"</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>Медицинская организация</td>\n",
       "      <td>7325031357</td>\n",
       "      <td>732501001</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>16.10.2016</td>\n",
       "      <td>18.04.2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1732</td>\n",
       "      <td>1.2.643.5.1.13.13.12.2.73.7370</td>\n",
       "      <td>None</td>\n",
       "      <td>ГОСУДАРСТВЕННОЕ УЧРЕЖДЕНИЕ ЗДРАВООХРАНЕНИЯ УЛЬ...</td>\n",
       "      <td>ГУЗ УОКБ</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>Медицинская организация</td>\n",
       "      <td>7300000043</td>\n",
       "      <td>732601001</td>\n",
       "      <td>...</td>\n",
       "      <td>ул</td>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>16.10.2016</td>\n",
       "      <td>01.03.2024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 48 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     id                             oid                     oldOid  \\\n",
       "0  1678  1.2.643.5.1.13.13.12.2.77.7799                       None   \n",
       "1  1683  1.2.643.5.1.13.13.12.2.22.1748                       None   \n",
       "2  1724  1.2.643.5.1.13.13.12.2.77.7798  1.2.643.5.1.13.3.25.77.31   \n",
       "3  1730  1.2.643.5.1.13.13.12.2.73.7369                       None   \n",
       "4  1732  1.2.643.5.1.13.13.12.2.73.7370                       None   \n",
       "\n",
       "                                            nameFull  \\\n",
       "0  ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕН...   \n",
       "1  КРАЕВОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ З...   \n",
       "2  ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ НАУЧНОЕ ...   \n",
       "3  ГОСУДАРСТВЕННОЕ УЧРЕЖДЕНИЕ ЗДРАВООХРАНЕНИЯ \"УЛ...   \n",
       "4  ГОСУДАРСТВЕННОЕ УЧРЕЖДЕНИЕ ЗДРАВООХРАНЕНИЯ УЛЬ...   \n",
       "\n",
       "                                           nameShort parentId  \\\n",
       "0  ФГБУ \"НМИЦ ОНКОЛОГИИ ИМ. Н.Н. БЛОХИНА\" МИНЗДРА...     None   \n",
       "1                                       КГБУЗ \"АКОД\"     None   \n",
       "2                                     ФГБНУ \"НИИ МТ\"     None   \n",
       "3                                         ГУЗ \"МИАЦ\"     None   \n",
       "4                                           ГУЗ УОКБ     None   \n",
       "\n",
       "  medicalSubjectId       medicalSubjectName         inn        kpp  ...  \\\n",
       "0                2  Медицинская организация  7724075162  772401001  ...   \n",
       "1                2  Медицинская организация  2225022328  222501001  ...   \n",
       "2                2  Медицинская организация  7719022912  771901001  ...   \n",
       "3                2  Медицинская организация  7325031357  732501001  ...   \n",
       "4                2  Медицинская организация  7300000043  732601001  ...   \n",
       "\n",
       "  prefixStreet house building struct parentOspOid ospOid childrenOspOid  \\\n",
       "0         None    24     None   None         None   None           None   \n",
       "1        тракт  110К     None   None         None   None           None   \n",
       "2           ул    12     None   None         None   None           None   \n",
       "3         None  None     None   None         None   None           None   \n",
       "4           ул     7     None   None         None   None           None   \n",
       "\n",
       "  deleteDate  createDate  modifyDate  \n",
       "0       None  16.10.2016  19.05.2023  \n",
       "1       None  16.10.2016  01.09.2023  \n",
       "2       None  16.10.2016  09.11.2023  \n",
       "3       None  16.10.2016  18.04.2023  \n",
       "4       None  16.10.2016  01.03.2024  \n",
       "\n",
       "[5 rows x 48 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# создаем пустой список\n",
    "rows = []\n",
    "# пройдемся по списку справочников попутно извлекая значения\n",
    "for i in range(len(data)):\n",
    "    dict_data = {d['column']: d['value'] for d in data[i]}\n",
    "    # дополняем список элементами - элемент списка - одна строка\n",
    "    rows.append(dict_data)\n",
    "# сохраняем все датафрейм\n",
    "df_data = pd.DataFrame(rows)\n",
    "df_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['id',\n",
       " 'oid',\n",
       " 'oldOid',\n",
       " 'nameFull',\n",
       " 'nameShort',\n",
       " 'parentId',\n",
       " 'medicalSubjectId',\n",
       " 'medicalSubjectName',\n",
       " 'inn',\n",
       " 'kpp',\n",
       " 'ogrn',\n",
       " 'regionId',\n",
       " 'regionName',\n",
       " 'organizationType',\n",
       " 'moDeptId',\n",
       " 'moDeptName',\n",
       " 'founder',\n",
       " 'deleteReason',\n",
       " 'moLevel',\n",
       " 'moAgencyKindId',\n",
       " 'moAgencyKind',\n",
       " 'profileAgencyKindId',\n",
       " 'profileAgencyKind',\n",
       " 'postIndex',\n",
       " 'cadastralNumber',\n",
       " 'latitude',\n",
       " 'longtitude',\n",
       " 'fiasVersion',\n",
       " 'aoidArea',\n",
       " 'aoidStreet',\n",
       " 'houseid',\n",
       " 'addrRegionId',\n",
       " 'addrRegionName',\n",
       " 'territoryCode',\n",
       " 'isFederalCity',\n",
       " 'areaName',\n",
       " 'prefixArea',\n",
       " 'streetName',\n",
       " 'prefixStreet',\n",
       " 'house',\n",
       " 'building',\n",
       " 'struct',\n",
       " 'parentOspOid',\n",
       " 'ospOid',\n",
       " 'childrenOspOid',\n",
       " 'deleteDate',\n",
       " 'createDate',\n",
       " 'modifyDate']"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(df_data.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 93544 entries, 0 to 93543\n",
      "Data columns (total 48 columns):\n",
      " #   Column               Non-Null Count  Dtype \n",
      "---  ------               --------------  ----- \n",
      " 0   id                   93544 non-null  object\n",
      " 1   oid                  93544 non-null  object\n",
      " 2   oldOid               1014 non-null   object\n",
      " 3   nameFull             93544 non-null  object\n",
      " 4   nameShort            93544 non-null  object\n",
      " 5   parentId             4348 non-null   object\n",
      " 6   medicalSubjectId     93544 non-null  object\n",
      " 7   medicalSubjectName   93544 non-null  object\n",
      " 8   inn                  93544 non-null  object\n",
      " 9   kpp                  84776 non-null  object\n",
      " 10  ogrn                 93544 non-null  object\n",
      " 11  regionId             93544 non-null  object\n",
      " 12  regionName           93544 non-null  object\n",
      " 13  organizationType     93544 non-null  object\n",
      " 14  moDeptId             93544 non-null  object\n",
      " 15  moDeptName           29210 non-null  object\n",
      " 16  founder              57745 non-null  object\n",
      " 17  deleteReason         4143 non-null   object\n",
      " 18  moLevel              74843 non-null  object\n",
      " 19  moAgencyKindId       87744 non-null  object\n",
      " 20  moAgencyKind         74894 non-null  object\n",
      " 21  profileAgencyKindId  28691 non-null  object\n",
      " 22  profileAgencyKind    28691 non-null  object\n",
      " 23  postIndex            46088 non-null  object\n",
      " 24  cadastralNumber      19274 non-null  object\n",
      " 25  latitude             75726 non-null  object\n",
      " 26  longtitude           75726 non-null  object\n",
      " 27  fiasVersion          0 non-null      object\n",
      " 28  aoidArea             55591 non-null  object\n",
      " 29  aoidStreet           52137 non-null  object\n",
      " 30  houseid              49698 non-null  object\n",
      " 31  addrRegionId         81687 non-null  object\n",
      " 32  addrRegionName       81686 non-null  object\n",
      " 33  territoryCode        0 non-null      object\n",
      " 34  isFederalCity        0 non-null      object\n",
      " 35  areaName             73329 non-null  object\n",
      " 36  prefixArea           55586 non-null  object\n",
      " 37  streetName           72075 non-null  object\n",
      " 38  prefixStreet         52076 non-null  object\n",
      " 39  house                69511 non-null  object\n",
      " 40  building             5939 non-null   object\n",
      " 41  struct               835 non-null    object\n",
      " 42  parentOspOid         0 non-null      object\n",
      " 43  ospOid               0 non-null      object\n",
      " 44  childrenOspOid       0 non-null      object\n",
      " 45  deleteDate           4143 non-null   object\n",
      " 46  createDate           93544 non-null  object\n",
      " 47  modifyDate           93544 non-null  object\n",
      "dtypes: object(48)\n",
      "memory usage: 34.3+ MB\n"
     ]
    }
   ],
   "source": [
    "df_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data['createDate'] = df_data['createDate'].apply(lambda x: pd.to_datetime(x, format='mixed', errors='coerce'))\n",
    "df_data['modifyDate'] = df_data['modifyDate'].apply(lambda x: pd.to_datetime(x, format='mixed', errors='coerce'))\n",
    "df_data['deleteDate'] = df_data['deleteDate'].apply(lambda x: pd.to_datetime(x, format='mixed', errors='coerce'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 93544 entries, 0 to 93543\n",
      "Data columns (total 48 columns):\n",
      " #   Column               Non-Null Count  Dtype         \n",
      "---  ------               --------------  -----         \n",
      " 0   id                   93544 non-null  object        \n",
      " 1   oid                  93544 non-null  object        \n",
      " 2   oldOid               1014 non-null   object        \n",
      " 3   nameFull             93544 non-null  object        \n",
      " 4   nameShort            93544 non-null  object        \n",
      " 5   parentId             4348 non-null   object        \n",
      " 6   medicalSubjectId     93544 non-null  object        \n",
      " 7   medicalSubjectName   93544 non-null  object        \n",
      " 8   inn                  93544 non-null  object        \n",
      " 9   kpp                  84776 non-null  object        \n",
      " 10  ogrn                 93544 non-null  object        \n",
      " 11  regionId             93544 non-null  object        \n",
      " 12  regionName           93544 non-null  object        \n",
      " 13  organizationType     93544 non-null  object        \n",
      " 14  moDeptId             93544 non-null  object        \n",
      " 15  moDeptName           29210 non-null  object        \n",
      " 16  founder              57745 non-null  object        \n",
      " 17  deleteReason         4143 non-null   object        \n",
      " 18  moLevel              74843 non-null  object        \n",
      " 19  moAgencyKindId       87744 non-null  object        \n",
      " 20  moAgencyKind         74894 non-null  object        \n",
      " 21  profileAgencyKindId  28691 non-null  object        \n",
      " 22  profileAgencyKind    28691 non-null  object        \n",
      " 23  postIndex            46088 non-null  object        \n",
      " 24  cadastralNumber      19274 non-null  object        \n",
      " 25  latitude             75726 non-null  object        \n",
      " 26  longtitude           75726 non-null  object        \n",
      " 27  fiasVersion          0 non-null      object        \n",
      " 28  aoidArea             55591 non-null  object        \n",
      " 29  aoidStreet           52137 non-null  object        \n",
      " 30  houseid              49698 non-null  object        \n",
      " 31  addrRegionId         81687 non-null  object        \n",
      " 32  addrRegionName       81686 non-null  object        \n",
      " 33  territoryCode        0 non-null      object        \n",
      " 34  isFederalCity        0 non-null      object        \n",
      " 35  areaName             73329 non-null  object        \n",
      " 36  prefixArea           55586 non-null  object        \n",
      " 37  streetName           72075 non-null  object        \n",
      " 38  prefixStreet         52076 non-null  object        \n",
      " 39  house                69511 non-null  object        \n",
      " 40  building             5939 non-null   object        \n",
      " 41  struct               835 non-null    object        \n",
      " 42  parentOspOid         0 non-null      object        \n",
      " 43  ospOid               0 non-null      object        \n",
      " 44  childrenOspOid       0 non-null      object        \n",
      " 45  deleteDate           0 non-null      datetime64[ns]\n",
      " 46  createDate           0 non-null      datetime64[ns]\n",
      " 47  modifyDate           0 non-null      datetime64[ns]\n",
      "dtypes: datetime64[ns](3), object(45)\n",
      "memory usage: 34.3+ MB\n"
     ]
    }
   ],
   "source": [
    "df_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['онкологическая', 'онкологический', None,\n",
       "       'медицинский информационно-аналитический',\n",
       "       'консультативно-диагностическая, в том числе детская',\n",
       "       'кожно-венерологический',\n",
       "       'общественного здоровья и медицинской профилактики',\n",
       "       'врачебно-физкультурный', 'судебно-медицинской экспертизы',\n",
       "       'санаторий', 'психоневрологическая, в том числе детская',\n",
       "       'профилактики и борьбы со СПИД', 'станция переливания крови',\n",
       "       'медицинский', 'станция скорой медицинской помощи',\n",
       "       'кардиологический',\n",
       "       'медицинский мобилизационных резервов \"Резерв\"',\n",
       "       'медицины катастроф',\n",
       "       'санатории для детей, в том числе для детей с родителями',\n",
       "       'психоневрологический', 'стоматологическая, в том числе детская',\n",
       "       'консультативно-диагностический, в том числе детский',\n",
       "       'патолого-анатомическое',\n",
       "       'психиатрическая (стационар) специализированного типа',\n",
       "       'медицинской статистики', 'психиатрическая, в том числе детская',\n",
       "       'лечебной физкультуры и спортивной медицины', 'наркологический',\n",
       "       'центр крови', 'инфекционная, в том числе детская',\n",
       "       'диагностический', 'перинатальный', 'противотуберкулезный',\n",
       "       'специализированные (по профилям медицинской помощи)',\n",
       "       'клинико-диагностическая', 'эндокринологический',\n",
       "       'клинико-диагностический',\n",
       "       'вспомогательных репродуктивных технологий',\n",
       "       'высоких медицинских технологий, в том числе по профилю медицинской помощи',\n",
       "       'санаторий-профилакторий', 'мануальной терапии',\n",
       "       'туберкулезная, в том числе детская', 'реабилитационный',\n",
       "       'медицинской реабилитации',\n",
       "       'медицинской реабилитации, в том числе детская',\n",
       "       'гинекологическая', 'многопрофильный', 'наркологическая',\n",
       "       'офтальмологическая', 'лечебно-реабилитационный',\n",
       "       'санаторный оздоровительный лагерь круглогодичного действия',\n",
       "       'медицинской реабилитации, в том числе детский',\n",
       "       'профессиональной патологии',\n",
       "       'медицинской реабилитации для воинов-интернационалистов',\n",
       "       'специализированных видов медицинской помощи', 'сурдологический',\n",
       "       'физиотерапевтическая',\n",
       "       'общей врачебной практики (семейной медицины)', 'гериатрический',\n",
       "       'медико-хирургический', 'лечебного и профилактического питания',\n",
       "       'охраны здоровья семьи и репродукции',\n",
       "       'медико-генетический (консультация)',\n",
       "       'медико-социальной реабилитации больных наркоманией',\n",
       "       'охраны материнства и детства', 'психотерапевтическая',\n",
       "       'медико-социальной экспертизы', 'офтальмологический', 'здоровья',\n",
       "       'гериатрическая',\n",
       "       'медико-социальной экспертизы и реабилитации инвалидов',\n",
       "       'психиатрическая (стационар) специализированного типа с интенсивным наблюдением',\n",
       "       'медицинский биофизический', 'реабилитации слуха',\n",
       "       'бактериологическая, в том числе по диагностике туберкулеза',\n",
       "       'психофизиологической диагностики',\n",
       "       'медицинской и социальной реабилитации, в том числе с отделением постоянного проживания инвалидов и детей-инвалидов с тяжелыми формами детского церебрального паралича, самостоятельно не передвигающихся и себя не обслуживающих;',\n",
       "       'охраны репродуктивного здоровья подростков',\n",
       "       'медицинской реабилитации для инвалидов и детей-инвалидов с последствиями детского церебрального паралича',\n",
       "       'бальнеологическая лечебница', 'диабетологический',\n",
       "       'военно-врачебной экспертизы', 'паллиативной медицинской помощи',\n",
       "       'курортная поликлиника', 'патологии речи и нейрореабилитации',\n",
       "       'грязелечебница', 'Центр (Медицинские организации особого типа)',\n",
       "       'Центр (Лечебно-профилактические медицинские организации)',\n",
       "       'Поликлиника',\n",
       "       'Медицинские организации скорой медицинской помощи и переливания крови',\n",
       "       'Санаторно-курортные организации'], dtype=object)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data['profileAgencyKind'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data = df_data.loc[:, ['id', 'oid', 'nameFull', 'nameShort', 'parentId', 'medicalSubjectId', 'medicalSubjectName', 'inn', 'kpp', 'ogrn', 'regionId', 'regionName',\n",
    " 'organizationType', 'moDeptId', 'moDeptName', 'founder', 'deleteReason', 'moLevel', 'moAgencyKindId', 'moAgencyKind', 'profileAgencyKindId', 'profileAgencyKind',\n",
    " 'postIndex', 'latitude', 'longtitude', 'aoidArea', 'aoidStreet', 'houseid', 'addrRegionId', 'areaName', 'prefixArea', 'streetName', 'prefixStreet',\n",
    " 'house', 'building', 'struct', 'deleteDate', 'createDate', 'modifyDate']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data = df_data.rename(columns={\n",
    "    'oid': 'mo_oid',\n",
    "    'longtitude': 'longitude'\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 93544 entries, 0 to 93543\n",
      "Data columns (total 39 columns):\n",
      " #   Column               Non-Null Count  Dtype         \n",
      "---  ------               --------------  -----         \n",
      " 0   id                   93544 non-null  object        \n",
      " 1   mo_oid               93544 non-null  object        \n",
      " 2   nameFull             93544 non-null  object        \n",
      " 3   nameShort            93544 non-null  object        \n",
      " 4   parentId             4348 non-null   object        \n",
      " 5   medicalSubjectId     93544 non-null  object        \n",
      " 6   medicalSubjectName   93544 non-null  object        \n",
      " 7   inn                  93544 non-null  object        \n",
      " 8   kpp                  84776 non-null  object        \n",
      " 9   ogrn                 93544 non-null  object        \n",
      " 10  regionId             93544 non-null  object        \n",
      " 11  regionName           93544 non-null  object        \n",
      " 12  organizationType     93544 non-null  object        \n",
      " 13  moDeptId             93544 non-null  object        \n",
      " 14  moDeptName           29210 non-null  object        \n",
      " 15  founder              57745 non-null  object        \n",
      " 16  deleteReason         4143 non-null   object        \n",
      " 17  moLevel              74843 non-null  object        \n",
      " 18  moAgencyKindId       87744 non-null  object        \n",
      " 19  moAgencyKind         74894 non-null  object        \n",
      " 20  profileAgencyKindId  28691 non-null  object        \n",
      " 21  profileAgencyKind    28691 non-null  object        \n",
      " 22  postIndex            46088 non-null  object        \n",
      " 23  latitude             75726 non-null  object        \n",
      " 24  longitude            75726 non-null  object        \n",
      " 25  aoidArea             55591 non-null  object        \n",
      " 26  aoidStreet           52137 non-null  object        \n",
      " 27  houseid              49698 non-null  object        \n",
      " 28  addrRegionId         81687 non-null  object        \n",
      " 29  areaName             73329 non-null  object        \n",
      " 30  prefixArea           55586 non-null  object        \n",
      " 31  streetName           72075 non-null  object        \n",
      " 32  prefixStreet         52076 non-null  object        \n",
      " 33  house                69511 non-null  object        \n",
      " 34  building             5939 non-null   object        \n",
      " 35  struct               835 non-null    object        \n",
      " 36  deleteDate           0 non-null      datetime64[ns]\n",
      " 37  createDate           0 non-null      datetime64[ns]\n",
      " 38  modifyDate           0 non-null      datetime64[ns]\n",
      "dtypes: datetime64[ns](3), object(36)\n",
      "memory usage: 27.8+ MB\n"
     ]
    }
   ],
   "source": [
    "df_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1) id, Уникальный идентификатор, Целочисленное, Обязательное\n",
    " 2) oid, OID, Строковое\n",
    "4) nameFull, Полное наименование, Строковое, Обязательное\n",
    " 5) nameShort, Сокращенное наименование, Строковое\n",
    "  19) deleteReason, Причина удаления записи, Строковое\n",
    " 18) deleteDate, Дата удаления записи, Дата\n",
    " 20) createDate, Дата создания записи, Дата\n",
    " 21) modifyDate, Дата последнего изменения записи, Дата\n",
    " 7) medicalSubjectId, Код субъекта системы здравоохранения, Целочисленное\n",
    " 12) regionId, Код региона РФ, Целочисленное\n",
    "15) moDeptId, Идентификатор ведомственной принадлежности, Целочисленное\n",
    "23) moAgencyKindId, Идентификатор вида деятельности, Целочисленное\n",
    "25) profileAgencyKindId, Идентификатор профиля деятельности, Целочисленное\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 93544 entries, 0 to 93543\n",
      "Data columns (total 13 columns):\n",
      " #   Column               Non-Null Count  Dtype         \n",
      "---  ------               --------------  -----         \n",
      " 0   id                   93544 non-null  object        \n",
      " 1   mo_oid               93544 non-null  object        \n",
      " 2   nameFull             93544 non-null  object        \n",
      " 3   nameShort            93544 non-null  object        \n",
      " 4   deleteReason         4143 non-null   object        \n",
      " 5   deleteDate           0 non-null      datetime64[ns]\n",
      " 6   createDate           0 non-null      datetime64[ns]\n",
      " 7   modifyDate           0 non-null      datetime64[ns]\n",
      " 8   medicalSubjectId     93544 non-null  object        \n",
      " 9   regionId             93544 non-null  object        \n",
      " 10  moDeptId             93544 non-null  object        \n",
      " 11  moAgencyKindId       87744 non-null  object        \n",
      " 12  profileAgencyKindId  28691 non-null  object        \n",
      "dtypes: datetime64[ns](3), object(10)\n",
      "memory usage: 9.3+ MB\n"
     ]
    }
   ],
   "source": [
    "dfmo = df_data.loc[:, ['id', 'mo_oid', 'nameFull', 'nameShort',\n",
    "'deleteReason','deleteDate', 'createDate', 'modifyDate', 'medicalSubjectId', 'regionId', 'moDeptId', 'moAgencyKindId', 'profileAgencyKindId']]\n",
    "dfmo.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1) id, Уникальный идентификатор, Целочисленное, Обязательное\n",
    " 2) oid, OID, Строковое\n",
    "4) nameFull, Полное наименование, Строковое, Обязательное\n",
    " 5) nameShort, Сокращенное наименование, Строковое\n",
    "6) parentId, Уникальный идентификатор родительской организации, Строковое;\n",
    "9) inn, ИНН, Строковое\n",
    " 10) kpp, КПП, Строковое\n",
    " 11) ogrn, ОГРН, Строковое\n",
    " 12) regionId, Код региона РФ, Целочисленное\n",
    "35) addrRegionId, Код региона, Целочисленное\n",
    "14) organizationType, Форма собственности, Целочисленное\n",
    "17) founder, Учредитель, Строковое\n",
    " 22) moLevel, Уровень организации, Строковое"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 93544 entries, 0 to 93543\n",
      "Data columns (total 13 columns):\n",
      " #   Column            Non-Null Count  Dtype \n",
      "---  ------            --------------  ----- \n",
      " 0   id                93544 non-null  object\n",
      " 1   mo_oid            93544 non-null  object\n",
      " 2   nameFull          93544 non-null  object\n",
      " 3   nameShort         93544 non-null  object\n",
      " 4   parentId          4348 non-null   object\n",
      " 5   inn               93544 non-null  object\n",
      " 6   kpp               84776 non-null  object\n",
      " 7   ogrn              93544 non-null  object\n",
      " 8   regionId          93544 non-null  object\n",
      " 9   addrRegionId      81687 non-null  object\n",
      " 10  organizationType  93544 non-null  object\n",
      " 11  founder           57745 non-null  object\n",
      " 12  moLevel           74843 non-null  object\n",
      "dtypes: object(13)\n",
      "memory usage: 9.3+ MB\n"
     ]
    }
   ],
   "source": [
    "dffmo_reg = df_data.loc[:, ['id', 'mo_oid', 'nameFull', 'nameShort', 'parentId',  'inn', 'kpp', 'ogrn', 'regionId', 'addrRegionId', 'organizationType', 'founder', 'moLevel']]\n",
    "dffmo_reg.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ведомственная принадлежность медицинской организации `mo_depart_affilation`\n",
    "15) moDeptId, Идентификатор ведомственной принадлежности, Целочисленное\n",
    " 16) moDeptName, Ведомственная принадлежность, Строковое"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "modept = df_data.loc[:, ['moDeptId',  'moDeptName']]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Вид деятельности медицинской организации `mo_type_agency_kind`\n",
    "23) moAgencyKindId, Идентификатор вида деятельности, Целочисленное\n",
    " 24) moAgencyKind, Вид деятельности, Строковое\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "agency = df_data.loc[:, ['moAgencyKindId',  'moAgencyKind']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "93544"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df_hist_dictionary.to_csv('aктуальные_версии_справочников.csv', sep=';', index=False)\n",
    "# df_data.to_csv('C:/Users/GlazkovOI/minzdrav/data/directory_frmo.csv', sep=';', index=False)\n",
    "df_data.to_sql(name='frmo_all', con=engine, if_exists='replace', index=False, chunksize=1000, schema='db01')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 93544 entries, 0 to 93543\n",
      "Data columns (total 39 columns):\n",
      " #   Column               Non-Null Count  Dtype         \n",
      "---  ------               --------------  -----         \n",
      " 0   id                   93544 non-null  object        \n",
      " 1   mo_oid               93544 non-null  object        \n",
      " 2   nameFull             93544 non-null  object        \n",
      " 3   nameShort            93544 non-null  object        \n",
      " 4   parentId             4348 non-null   object        \n",
      " 5   medicalSubjectId     93544 non-null  object        \n",
      " 6   medicalSubjectName   93544 non-null  object        \n",
      " 7   inn                  93544 non-null  object        \n",
      " 8   kpp                  84776 non-null  object        \n",
      " 9   ogrn                 93544 non-null  object        \n",
      " 10  regionId             93544 non-null  object        \n",
      " 11  regionName           93544 non-null  object        \n",
      " 12  organizationType     93544 non-null  object        \n",
      " 13  moDeptId             93544 non-null  object        \n",
      " 14  moDeptName           29210 non-null  object        \n",
      " 15  founder              57745 non-null  object        \n",
      " 16  deleteReason         4143 non-null   object        \n",
      " 17  moLevel              74843 non-null  object        \n",
      " 18  moAgencyKindId       87744 non-null  object        \n",
      " 19  moAgencyKind         74894 non-null  object        \n",
      " 20  profileAgencyKindId  28691 non-null  object        \n",
      " 21  profileAgencyKind    28691 non-null  object        \n",
      " 22  postIndex            46088 non-null  object        \n",
      " 23  latitude             75726 non-null  object        \n",
      " 24  longitude            75726 non-null  object        \n",
      " 25  aoidArea             55591 non-null  object        \n",
      " 26  aoidStreet           52137 non-null  object        \n",
      " 27  houseid              49698 non-null  object        \n",
      " 28  addrRegionId         81687 non-null  object        \n",
      " 29  areaName             73329 non-null  object        \n",
      " 30  prefixArea           55586 non-null  object        \n",
      " 31  streetName           72075 non-null  object        \n",
      " 32  prefixStreet         52076 non-null  object        \n",
      " 33  house                69511 non-null  object        \n",
      " 34  building             5939 non-null   object        \n",
      " 35  struct               835 non-null    object        \n",
      " 36  deleteDate           0 non-null      datetime64[ns]\n",
      " 37  createDate           0 non-null      datetime64[ns]\n",
      " 38  modifyDate           0 non-null      datetime64[ns]\n",
      "dtypes: datetime64[ns](3), object(36)\n",
      "memory usage: 27.8+ MB\n"
     ]
    }
   ],
   "source": [
    "df_data.info()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
